﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.Data;
using System.Collections;
using Sys.SqlServer.Smo.Connection;

namespace Sys.SqlServer.Smo
{
    public class SqlSerializer
    {
        #region Actions

        #region Serialization

        public Script SerializeSchema(ConnectionString connectionString, List<Table> tables, List<View> views, List<StoredProcedure> procedures, List<UserDefinedFunction> functions, List<Trigger> triggers)
        {
            Script result = new Script();

            using (SqlConnection c = new SqlConnection(connectionString.ToString()))
            {
                List<SqlSmoObject> items = new List<SqlSmoObject>();

                Server server = new Server(new ServerConnection(c));

                Database db = server.Databases[connectionString.Database];

                Scripter script = new Scripter(server);

                Scriptlet scriplet;

                foreach (Table s in tables)
                {
                    scriplet = new Scriptlet(string.Format("{0}.{1}", s.Schema, s.Name), ScriptType.Schema, ScriptObject.Table);
                    scriplet.Data = ScriptSchema(server, s);

                    result.Data.Add(scriplet);
                }

                foreach (View s in views)
                {
                    scriplet = new Scriptlet(string.Format("{0}.{1}", s.Schema, s.Name), ScriptType.Schema, ScriptObject.View);
                    scriplet.Data = ScriptSchema(server, s);

                    result.Data.Add(scriplet);
                }

                foreach (StoredProcedure s in procedures)
                {
                    scriplet = new Scriptlet(string.Format("{0}.{1}", s.Schema, s.Name), ScriptType.Schema, ScriptObject.StoredProcedure);
                    scriplet.Data = ScriptSchema(server, s);

                    result.Data.Add(scriplet);
                }

                foreach (UserDefinedFunction s in functions)
                {
                    scriplet = new Scriptlet(string.Format("{0}.{1}", s.Schema, s.Name), ScriptType.Schema, ScriptObject.Function);
                    scriplet.Data = ScriptSchema(server, s);

                    result.Data.Add(scriplet);
                }

                foreach (Trigger s in triggers)
                {
                    scriplet = new Scriptlet(s.Name, ScriptType.Schema, ScriptObject.Trigger);
                    scriplet.Data = ScriptSchema(server, s);

                    result.Data.Add(scriplet);
                }
            }

            return result;
        }

        public Script SerializeSchema(ConnectionString connectionString, string[] tables, string[] views, string[] procedures, string[] functions, string[] triggers)
        {
            Script result = new Script();

            using (SqlConnection c = new SqlConnection(connectionString.ToString()))
            {
                List<SqlSmoObject> items = new List<SqlSmoObject>();

                Server server = new Server(new ServerConnection(c));

                Database db = server.Databases[connectionString.Database];

                Scripter script = new Scripter(server);

                Scriptlet scriplet;

                foreach (string s in tables)
                {
                    string[] x = s.Split('.');

                    scriplet = new Scriptlet(s, ScriptType.Schema, ScriptObject.Table);
                    scriplet.Data = ScriptSchema(server, db.Tables[x[1], x[0]]);

                    result.Data.Add(scriplet);
                }

                foreach (string s in views)
                {
                    string[] x = s.Split('.');

                    scriplet = new Scriptlet(s, ScriptType.Schema, ScriptObject.View);
                    scriplet.Data = ScriptSchema(server, db.Views[x[1], x[0]]);

                    result.Data.Add(scriplet);
                }

                foreach (string s in procedures)
                {
                    string[] x = s.Split('.');

                    scriplet = new Scriptlet(s, ScriptType.Schema, ScriptObject.StoredProcedure);
                    scriplet.Data = ScriptSchema(server, db.StoredProcedures[x[1], x[0]]);

                    result.Data.Add(scriplet);
                }

                foreach (string s in functions)
                {
                    string[] x = s.Split('.');

                    scriplet = new Scriptlet(s, ScriptType.Schema, ScriptObject.Function);
                    scriplet.Data = ScriptSchema(server, db.UserDefinedFunctions[x[1], x[0]]);

                    result.Data.Add(scriplet);
                }

                foreach (string s in triggers)
                {
                    scriplet = new Scriptlet(s, ScriptType.Schema, ScriptObject.Trigger);
                    scriplet.Data = ScriptSchema(server, db.Triggers[s]);

                    result.Data.Add(scriplet);
                }
            }

            return result;
        }

        public Script SerializeData(ConnectionString connectionString, string table)
        {
            Script result = new Script();

            using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
            {
                connection.Open();

                int i = 0;

                string query = string.Empty;
                
                string column = string.Empty;
                string columndata = string.Empty;

                string key = string.Empty;
                string keydata = string.Empty;

                key = GetPrimaryKey(connectionString, table);

                SqlCommand command = new SqlCommand(string.Format("SELECT * FROM [{0}]", table), connection);

                SqlDataReader reader = command.ExecuteReader();

                if (reader != null)
                {
                    while (reader.Read())
                    {
                        i++;
                        
                        query = string.Empty;
                        columndata = string.Empty;
                        column = string.Empty;

                        for (int j = 0; j < reader.FieldCount; j++)
                        {
                            if (j > 0)
                            {
                                column += string.Format(", [{0}]", reader.GetName(j).ToString());
                                columndata += string.Format(", '{0}'", reader[j].ToString());
                            }
                            else
                            {
                                column += reader.GetName(j).ToString();
                                columndata += string.Format("'{0}'", reader[j].ToString());
                            }

                            if (key == reader.GetName(j).ToString())
                                keydata = reader[j].ToString();

                            if (keydata != null)
                                query = GetInsertQuery(column, columndata, table);
                        }

                        WriteScript(table, query, keydata, key, i);
                    }
                }
            }

            return result;
        }

        public Script SerializeDatabase(ConnectionString connectionString)
        {
            Script result = new Script();

            using (SqlConnection c = new SqlConnection(connectionString.ToString()))
            {
                List<SqlSmoObject> items = new List<SqlSmoObject>();

                Server server = new Server(new ServerConnection(c));

                Database db = server.Databases[connectionString.Database];

                if (db != null)
                {
                    Transfer t = new Transfer(db);
                    t.CopyAllObjects = true;
                    t.Options.WithDependencies = true;

                    StringBuilder sb = new StringBuilder();

                    foreach (string str in t.ScriptTransfer())
                        sb.AppendLine(str);

                    Scriptlet scriptlet = new Scriptlet(connectionString.Database, ScriptType.Schema, ScriptObject.Database);
                    scriptlet.Data = sb.ToString();

                    result.Data.Add(scriptlet);
                }
            }

            return result;
        }

        #endregion

        #region Deserialization

        public void DeserializeSchema(ConnectionString connectionString, Script s)
        {
            using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
            {
                SqlCommand command = null;

                foreach (Scriptlet sc in s.Data)
                {
                    command = new SqlCommand(sc.Data, connection);
                    command.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #endregion

        #region Helpers

        private string ScriptSchema(Server server, SqlSmoObject o)
        {
            string result = string.Empty;

            if (o != null)
            {
                Scripter s = new Scripter();
                s.Server = server;
                s.Options.ToFileOnly = false;
                s.Options.AppendToFile = false;
                s.Options.IncludeHeaders = false;
                s.Options.IncludeDatabaseContext = true;
                s.Options.ScriptSchema = true;
                s.Options.ScriptDrops = false;
                s.Options.WithDependencies = true;
                s.Options.SchemaQualify = true;
                s.Options.SchemaQualifyForeignKeysReferences = true;
                s.Options.ScriptBatchTerminator = true;

                StringBuilder sb = new StringBuilder();

                foreach (string str in s.Script(new SqlSmoObject[] { o }))
                    sb.AppendLine(str);

                result = sb.ToString();
            }

            return result;
        }

        private string GetPrimaryKey(ConnectionString connectionString, string table)
        {
            string result = string.Empty;

            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString.ToString()))
                {
                    SqlCommand command = new SqlCommand();

                    connection.Open();

                    command = new SqlCommand("sp_pkeys", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value = table;

                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                        result = reader[3].ToString();
                }
            }
            catch (Exception e)
            {
                result = string.Empty;
            }

            return result;
        }

        private string GetInsertQuery(string column, string columndata, string table)
        {
            return string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", table, column , columndata);
        }

        private void WriteScript(string table, string query, string keyvalues, string primarykey, int i)
        {
            //StreamWriter sw = new StreamWriter(@"d:\script1.txt", true, Encoding.UTF8);
            //sw.Write(query);
            //sw.Close();
        }

        #endregion
    }
}